<?php
       
	$module_path = drupal_get_path('module','whatsnew');
	drupal_add_css($module_path . '/css/whatsnew.css');
	
function whatsnewhome(){
global $language;
global $base_url;

$output = "<div></div>"; //print date('2012-05-14T00:00:00');
  $lastmonth = strtotime('-1 year');
  $lastmonth = date("Y-m-dTh:i:m", $lastmonth);
  $currentdate = strtotime('now');
  $currentdate = date("c", $currentdate);
  $currentdate =explode('+',$currentdate);//print $lastmonth[0].$t;  print $currentdate[0];
  $cur_month = date('m');
  $cur_year = date('Y');
  $docheader = "<div id='viewmod_whatsnew' class='view-header'>
				  <div class = 'inner_tab_content'>
				 	 <div class='inner_li' class='view-header'><a class='view-header' href='".$base_url."/whatsnew/impt'>".t('Important')."</a></div>
				  </div>
				</div>
				<div class='whatsnew_container'><div id='innerResHead'>";
	my_db_change(1);
	$sql = db_query("select a.*,f.filepath from (select n.nid,c.*,n.title,s.* from field_data_field_whatsnew_date as c,node as n ,field_data_field_whatsnew_upload as s
					 where n.nid=c.entity_id and n.vid=c.revision_id and c.entity_id =s.entity_id and c.revision_id = s.revision_id  and n.status=1
					and (n.language = '".$language->language."' OR n.language = '') AND  (date(c.field_whatsnew_date_value) > ('$lastmonth')) 
					order by c.field_whatsnew_date_value DESC,n.title ASC) as a LEFT JOIN files as f ON a.field_whatsnew_upload_fid = f.fid");
	/*$subquery = db_select('files', 'f');			 
		$subquery->fields('f',array('fid','filepath'));
		$subquery->orderBy('f.fid', 'desc');
	$query = db_select('node', 'n');
		$query->fields('n', array('title','nid','vid'));
		$query->condition('n.status', '1');
		$query->join('field_data_field_whatsnew_date', 'c', 'n.nid = c.entity_id and date(c.field_whatsnew_date_value) >  \'' .$lastmonth .'\'');
		$query->addJoin('LEFT','field_data_field_whatsnew_upload', 's', 's.entity_id = c.entity_id ');
		$query->addJoin('LEFT','field_data_field_whatsnew_available', 'a', 'a.entity_id = c.entity_id ');
		$query->addJoin('LEFT','field_data_field_whatsnew_description', 'd', 'd.entity_id = c.entity_id');
		$query->addJoin('LEFT','field_data_field_whatsnew_url', 'u', 'u.entity_id = c.entity_id ');		
		$query->addJoin('LEFT','field_data_field_whatsnew_offline', 'fo', 'fo.entity_id = n.nid ');	
		$query->orderBy('c.field_whatsnew_date_value','DESC');
		$query->orderBy('n.nid','DESC');
		$query->fields('c',array('field_whatsnew_date_value'));
		$query->fields('s',array('field_whatsnew_upload_fid'));
		$query->fields('a',array('field_whatsnew_available_value'));
		$query->fields('d',array('field_whatsnew_description_value'));
		$query->fields('u',array('field_whatsnew_url_value'));
		$query->fields('fo',array('field_whatsnew_offline_value'));
		$query->leftjoin($subquery, 'f', 'f.fid = s.field_whatsnew_upload_fid');
		$query->fields('f',array('fid','filepath'));
		$query->distinct();//LIMIT to 2 records	  
		$result = $query->extend('PagerDefault')->limit(30)->execute();
		$num_of_results = $result->rowCount();*/
$query = new EntityFieldQuery();
	$query->entityCondition('entity_type', 'node')
		->entityCondition('bundle', 'whatsnew')
		->propertyCondition('status',1)
		->fieldCondition('field_whatsnew_date', 'value', $lastmonth, '>')
		->fieldOrderBy('field_whatsnew_date', 'value', 'DESC')
		->pager(25,0); 

	$result = $query->execute();
	 
	$nid = current($result['node'])->nid;
	 
	if (isset($result['node'])) {
		$news_items_nids = array_keys($result['node']);
		//$news_items = entity_load('node', $news_items_nids);
		$news_items = node_load_multiple($news_items_nids, array('type' => 'whatsnew'));
	}


		foreach($news_items as $whatsnewlist){
			//print "<pre>";print_r($whatsnewlist);print "</pre>"; 
		
			if($whatsnewlist->field_whatsnew_available['und'][0]['value'] == 'download')  {
				 $link = $whatsnewlist->field_whatsnew_upload['und'][0]['uri'];		
				$wrapper = file_stream_wrapper_get_instance_by_uri($link);
				if($wrapper == true){
					 $pathfile = $wrapper->getDirectoryPath() . "/" . file_uri_target($link);
				}
			 	$filepath="'http://cms.tn.gov.in/tncms_latest/$pathfile'";
			}
			elseif($whatsnewlist->field_whatsnew_available['und'][0]['value'] == 'online')  {
				$filepath = $whatsnewlist->field_whatsnew_url['und'][0]['value'];
				$filepath ="'$filepath'";
			}
			elseif($whatsnewlist->field_whatsnew_available['und'][0]['value'] == 'offline')  {
            	$filepath = $whatsnewlist->field_whatsnew_offline['und'][0]['value']; 
				$filepath ="'$filepath'";
			}
				$date = date ('F j,Y',strtotime($whatsnewlist->field_whatsnew_date['und'][0]['value']) );
				$title = $whatsnewlist-> title;
              	if(date('F j,Y') == $date){
					$title = '<b>'.$whatsnewlist-> title.'</b>';
				}
			if($whatsnewlist->field_whatsnew_date['und'][0]['value']){
			$res .="<div style='padding-top:3px; padding-bottom:3px;' id='w_res'><div style='float:left;' class='w_res_date'>".$date."</div> ";
			/*if($whatsnewlist->field_whatsnew_available_value == 'online') {   
				$res .="<div style='float:left;' class='w_res_title'><a href='".$filepath."'>".$title."</a></div>";
			}*/   
				$res .='<div style="float:left;" class="w_res_title"><a href="javascript:void(0);" onClick="return tnportal_ext_window('.$filepath.');">'.$title.'</a>
											</div><div class="clr"></div></div><div class="clr"></div>';						           
			}
		}
        if(!empty($num_of_results)) { 
			$res1 .="<div class='w_date'>Date</div>
							<div class='w_title'>Title</div>
							<div class='clr'></div>			
					</div>";
		}
		my_db_change(0);
  $res .= theme('pager'); 
  $archive_form= "</div><div id=\"docArchive\" style=\"text-align:right;\"><span style='font-weight:bold'> Archives:</span> ".Whatsnew_archive_links()."</div>"; 
 return $docheader.$output.$res1.$res.$archive_form;
}
function archives_whatsnew($year='',$month=''){
global $language;
global $base_url;

 $year= arg(2);
 $month = arg(3);
	$lastmonth = strtotime('-1 month');
	$currentdate = strtotime(date("d-m-y") );
        $cur_month =  $month;
        $cur_year = $year;
        $cur_year_qry = '';
        $cur_month_qry = '';
        $cur_arch_qry = '';
	    if(!empty($cur_year)){          
        	$cur_arch_qry = "and extract(YEAR from date(c.field_whatsnew_date_value)) =". $cur_year;
		}if(!empty($cur_year) && !empty($cur_month)){
	    	$cur_arch_qry .= " and extract(MONTH from date(c.field_whatsnew_date_value)) =". $cur_month;
		}
	
$output = "<div id='viewmod_whatsnew' class='view-header'>
				  <div class = 'inner_tab_content'>
				 	 <div class='inner_li' class='view-header'><a class='view-header' href='".$base_url."/whatsnew/impt'>".t('Important')."</a></div>
				  </div>
				</div>
				<div class='whatsnew_container'><div id='innerResHead'>";
				
		my_db_change(1);
		$subquery = db_select('files', 'f');			 
			$subquery->fields('f',array('fid','filepath'));
			$subquery->orderBy('f.fid', 'desc');
	    $query = db_select('node', 'n');
			$query->fields('n', array('title','nid'));
			$query->condition('n.status', '1');
			if(!empty($cur_year) && !empty($cur_month)){
				  $query->join('field_data_field_whatsnew_date', 'c', 'n.nid = c.entity_id and n.vid = c.revision_id and extract(MONTH from date(c.field_whatsnew_date_value)) = \''.$cur_month.'\'');
     		}  if(!empty($cur_year)){
			$query->join('field_data_field_whatsnew_date', 'c', 'n.nid = c.entity_id and n.vid = c.revision_id and extract(YEAR from date(c.field_whatsnew_date_value)) = \''.$cur_year.'\'');
			}
			$query->addJoin('LEFT','field_data_field_whatsnew_upload', 's', 's.entity_id = c.entity_id and s.revision_id = c.revision_id');
			$query->addJoin('LEFT','field_data_field_whatsnew_available', 'a', 'a.entity_id = c.entity_id and a.revision_id = c.revision_id');
			$query->addJoin('LEFT','field_data_field_whatsnew_description', 'd', 'd.entity_id = c.entity_id and d.revision_id = c.revision_id');
			$query->addJoin('LEFT','field_data_field_whatsnew_url', 'u', 'u.entity_id = c.entity_id and u.revision_id = c.revision_id');		
			$query->addJoin('LEFT','field_data_field_whatsnew_offline', 'of', 'of.entity_id = c.entity_id ');		
			$query->orderBy('c.field_whatsnew_date_value','DESC');
			$query->orderBy('n.title','ASC');
			$query->fields('c',array('field_whatsnew_date_value'));
			$query->fields('s',array('field_whatsnew_upload_fid'));
			$query->fields('a',array('field_whatsnew_available_value'));
			$query->fields('d',array('field_whatsnew_description_value'));
			$query->fields('u',array('field_whatsnew_url_value'));
			$query->fields('of',array('field_whatsnew_offline_value'));
			$query->leftjoin($subquery, 'f', 'f.fid = s.field_whatsnew_upload_fid');
			$query->fields('f',array('fid','filepath'));
			$query->distinct();//LIMIT to 2 records	  
			$result = $query->extend('PagerDefault')->limit(30)->execute();
			$num_of_results = $result->rowCount();
		foreach($result as $whatsnewlist){ // print_r($result);
		if($whatsnewlist->field_whatsnew_available_value == 'download')  {
				$filepath = $whatsnewlist->filepath;  
				$filepath="'http://cms.tn.gov.in/$filepath'";
			}
			elseif($whatsnewlist->field_whatsnew_available_value == 'online')  {
				$filepath = $whatsnewlist->field_whatsnew_url_value;
				$filepath ="'$filepath'";
			}			
			elseif($whatsnewlist->field_whatsnew_available_value == 'offline')  {
            	$filepath = $whatsnewlist->field_whatsnew_offline_value; 
				$filepath ="'$filepath'";
			}
				$date = date ('F j,Y',strtotime($whatsnewlist->field_whatsnew_date_value) );
				$title = $whatsnewlist-> title;
              	if(date('F j,Y') == $date){
					$title = '<b>'.$whatsnewlist-> title.'</b>';
				}
			$res .="<div style='padding-top:3px; padding-bottom:3px;' id='w_res'><div style='float:left;' class='w_res_date'>".$date."</div> ";
			$res .='<div style="float:left;" class="w_res_title"><a href="javascript:void(0);" onClick="return tnportal_ext_window('.$filepath.');">'.$title.'</a>
											</div><div class="clr"></div></div><div class="clr"></div>';						           
		}
		if(!empty($year)){
			$month_array = array('Jan'=>'01','Feb'=>'02','Mar'=>'03','Apr'=>'04','May'=>'05','Jun'=>'06','July'=>'07','Aug'=>'08','Sep'=>'09','Oct'=>'10','Nov'=>'11','Dec'=>'12',				
			);                                    
			$month_exp = '';
			foreach ($month_array as $key => $value){
				if(!empty($month)){
					 $month = (int) $month;
					 $month_exp = date( 'F', mktime(0, 0, 0, $month) );
				}
		  		 $OPmonth .= l($key,"whatsnew/archives/".$year."/".$value)."&nbsp;&nbsp;";
			}
			$output .='<div class="arch"><div style="height:10px">&nbsp;&nbsp;</div>';
			$output .= '<div style="font-weight:bold">Archives: <span style="color:red;">'.$year.'&nbsp;&nbsp;'.t($month_exp).'</span></div>';
			$output .= '<div>'.$OPmonth.'</div></div>';
		}
		 if(!empty($num_of_results)) { 
			$res1 .="<div class='w_date'>Date</div>
							<div class='w_title'>Title</div>
							<div class='clr'></div>			
					</div>";
		}
		else{
			$res1 .='<div class=" no_record">'.t('No Results Found').' </div>';
 		}
		my_db_change(0);
	  $res .= theme('pager'); 
	  $archive_form= "</div></div><div id=\"docArchive\" style=\"text-align:right;\"><span style='font-weight:bold'> Archives:</span> ".Whatsnew_archive_links()."</div>"; 
return $output.$res1.$res.$archive_form;

}
function Whatsnew_archive_links(){
    $sql = "SELECT DISTINCT extract(YEAR from date(field_whatsnew_date_value)) as year   from field_data_field_whatsnew_date order by year DESC";
	my_db_change(1);
  	$result = db_query($sql);
	my_db_change(0);
    
    foreach($result as $links) {
    //if($links->year == '2010')
    //    continue;
    $OPYear .= l($links->year,"whatsnew/archives/".$links->year)."&nbsp;&nbsp;";
    }
   return $OPYear;
    
}
?>



